package gu.sql2java.manager;

import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;

import static com.google.common.base.MoreObjects.firstNonNull;
import static com.google.common.base.Preconditions.*;
import static gu.sql2java.SimpleLog.*;
import static gu.sql2java.utils.JDBCUtility.getJavaClass;

import java.math.BigDecimal;
import java.net.URL;
import java.nio.ByteBuffer;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.concurrent.Callable;
import java.util.concurrent.atomic.AtomicLong;

import javax.sql.DataSource;

import com.google.common.base.Strings;
import com.google.common.base.Throwables;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.ParseException;

import gu.sql2java.BaseBean;
import gu.sql2java.BaseRow;
import gu.sql2java.Constant;
import gu.sql2java.ListenerContainer;
import gu.sql2java.RowMetaData;
import gu.sql2java.SqlRunner;
import gu.sql2java.TableManager;
import gu.sql2java.UnnameRow;
import gu.sql2java.UnnameRowMetaData;
import gu.sql2java.ListenerContainer.FireType;
import gu.sql2java.TableManager.Action;
import gu.sql2java.exception.DaoException;
import gu.sql2java.exception.DataAccessException;
import gu.sql2java.exception.RuntimeDaoException;
import gu.sql2java.geometry.GeometryDataCodec;
import gu.sql2java.geometry.GeometryDataCodecs;
import gu.sql2java.manager.c3p0.C3p0DataSourceFactory;
import gu.sql2java.manager.druid.DruidDataSourceFactory;
import gu.sql2java.manager.parser.ParserSupport;
import gu.sql2java.manager.parser.SqlSyntaxNormalizer;
import gu.sql2java.manager.parser.SqlSyntaxNormalizers;
import gu.sql2java.manager.parser.StatementCache;
import gu.sql2java.manager.sqlite.SqliteDataSourceFactory;
import gu.sql2java.pagehelper.Page;
import gu.sql2java.pagehelper.PageHelper;


/**
 * The Manager provides connections and manages transactions transparently.
 * <br>
 * It is a singleton, you get its instance with the getInstance() method.
 * All of the XxxxManager classes use the Manager to get database connections.
 * Before doing any operation, you must pass either a
 * datasource or a jdbc driver/url/username/password.
 * @author guyadong
 */
final class Manager implements Constant,SqlRunner
{
	static final Map<String, Manager> managers = new Hashtable<>();
	static final Map<String, Manager> aliasManagers = new Hashtable<>();

    private static class Singleton{
        private static final Manager INSTANCE = new Manager();
    }
    private final ThreadLocal<Connection> transactionConnection = new ThreadLocal<Connection>();
    private final ThreadLocal<Connection> localConnection = new ThreadLocal<Connection>();
    private final ThreadLocal<Integer> nestedCount = new ThreadLocal<Integer>();

    private volatile DataSource dataSource = null;
    private volatile AutoKeyRetrieveType  retrieveType;
    private volatile ResultSetTypeCast extensiveResultSetTypeCast;
    private volatile DataSourceFactory dataSourceFactory;
    private volatile String productName;
    private volatile DatabaseMetaData databaseMetaData;
    private volatile StatementCache statementCache;
    private volatile GeometryDataCodec geometryDataCodec;
    final DataSourceConfig config;
    /**
     * Returns the manager singleton instance.
     */
    private Manager()
    {
        this(new DataSourceConfig());
    }

   Manager(Properties databaseProperties)
    {
	   this(DataSourceConfig.createConfig(databaseProperties));
    }
   Manager(DataSourceConfig config)
   {
	   this.config = config;
	   managers.put(checkNotNull(config.jdbcUrl,"jdbcUrl is null"), this);
	   aliasManagers.put(this.config.alias, this);
   }

    /**
     * Returns the manager singleton instance.
     */
    static Manager getInstance()
    {
        return Singleton.INSTANCE;
    }
    
    /** dispose pool */
    void disposePool(){
    	if(null != dataSourceFactory){
    		dataSourceFactory.destroy(dataSource);
    		dataSourceFactory = null;
    		dataSource = null;
    	}
    }
    private DataSourceFactory getDataSourceFactory(){
    	if(null == dataSourceFactory){
    		synchronized (this) {
    			if(null == dataSourceFactory){
    				switch (config.dataSource) {
					case DataSourceConfig.DT_SQLITE:
						dataSourceFactory = new SqliteDataSourceFactory();
						break;
					case DataSourceConfig.DT_C3P0:
						dataSourceFactory = new C3p0DataSourceFactory();
						break;
					case DataSourceConfig.DT_DRUID:
						dataSourceFactory = new DruidDataSourceFactory();
						break;
					default:
						throw new RuntimeException("UNSUPPORT DATA SOURCE TYPE:" + config.dataSource);
					}
    			}
			}
    	}
    	return dataSourceFactory;
    }
    private DatabaseMetaData getDatabaseMetaData(Connection c) throws SQLException{
        if(null == databaseMetaData){
            synchronized (this) {
                if(null == databaseMetaData){
                    this.databaseMetaData = c.getMetaData();
                    this.productName = this.databaseMetaData.getDatabaseProductName();
                }
            }
        }
        return this.databaseMetaData;
    }
    DatabaseMetaData getDatabaseMetaData() throws RuntimeDaoException{
        if(null == databaseMetaData){
            synchronized (this) {
                if(null == databaseMetaData){
                    try {
                        initDataSourceLazy();
                    } catch (SQLException e) {
                        throw new RuntimeDaoException(e);
                    }
                }
            }
        }
        return databaseMetaData;
    }

    String getProductName() throws RuntimeDaoException{
    	if(null == productName){
    		synchronized (this) {
    			if(null == productName){
    			    getDatabaseMetaData();
    			}
    		}
    	}
    	return productName;
    }
    StatementCache getStatementCache() {
        // double check
        if(null == statementCache){
            synchronized (this) {
                if(null == statementCache){
                    SqlFormatter sqlFormatter = SqlFormatters.formatterOf(getProductName());
                    SqlSyntaxNormalizer normalizer = SqlSyntaxNormalizers.normalizerOf(getProductName());
                    statementCache = new StatementCache(sqlFormatter, normalizer);
                }
            }
        }
        return statementCache;
    }

    GeometryDataCodec getGeometryDataCodec() {
        // double check
        if(null == geometryDataCodec) {
            synchronized (this) {
                if(null == geometryDataCodec) {
                    geometryDataCodec = GeometryDataCodecs.getGeometryDataCodec(getProductName());
                }
            }
        }
        return geometryDataCodec;
    }
    private void initDataSourceLazy() throws SQLException,RuntimeDaoException{
    	// double check
    	if (dataSource == null){
    		synchronized (this) {
    			if (dataSource == null){
    				try {
    					dataSource = getDataSourceFactory().createDataSource(config); 
    					/* initialize {@link #extensiveResultSetTypeCast} for extensive type cast of database engine,such SQLite */
    					{
    						try (Connection c = getConnection0()){
    							if("SQLite".equals(getDatabaseMetaData(c).getDatabaseProductName())){
    								extensiveResultSetTypeCast = ResultSetTypeCastSqliteImpl.SQLITE_CAST;
    							}
    						}
    					}
					} catch (RuntimeException e) {
						Throwables.throwIfInstanceOf(e, RuntimeDaoException.class);
			        	throw new RuntimeDaoException(e);
					}
    			}
    		}
    	}
    }
    void setDataSource(DataSource dataSource){
    	this.dataSource = dataSource;
    }
    void setDataSourceFactory(DataSourceFactory dataSourceFactory){
    	this.dataSourceFactory = dataSourceFactory;
    	setDataSource(null);
    }
    private Connection getConnection0() throws SQLException{
    	return dataSource.getConnection();
    }
    /**
     * Gets an auto commit connection.
     * <br>
     * Normally you do not need this method that much ;-)
     *
     * @return an auto commit connection
     */
    Connection getConnection() throws SQLException
    {
        Connection tc = transactionConnection.get();
        if (tc != null) {
            return tc;
        }
        Connection c = localConnection.get();
        if(null != c) {
            nestedCount.set(nestedCount.get() + 1);
            return c;
        }
    	initDataSourceLazy();
    	c = getConnection0();
    	localConnection.set(c);
    	nestedCount.set(0);
    	return c;
    }

    /**
     * Releases the database connection.
     * <br>
     * Normally you should not need this method ;-)
     */
    void releaseConnection(Connection c)
    {
        Connection tc = transactionConnection.get();
        if (tc != null){
            return;
        }
        if(c == localConnection.get()) {
            if(null != nestedCount.get() && nestedCount.get() > 0) {
                nestedCount.set(nestedCount.get() - 1);
                return ;
            }else {
                nestedCount.remove();
                localConnection.remove();
            }
        }
        try{
            if (c != null){
                c.close();
            }
        }catch (SQLException e){
            throw new RuntimeDaoException(e);
        }
    }

    /**
     * Initiates a database transaction.
     * <br>
     * When working within a transaction, you should invoke this method first.
     * The connection is returned just in case you need to set the isolation level.
     *
     * @return a non-auto commit connection with the default transaction isolation level
     */
    private Connection beginTransaction() throws SQLException
    {
        Connection c = this.getConnection();
        c.setAutoCommit(false);
        transactionConnection.set(c);
        return c;
    }

    /**
     * Releases connection used for the transaction and performs a commit or rollback.
     *
     * @param commit tells whether this connection should be committed
     *        true for commit(), false for rollback()
     */
    private void endTransaction(boolean commit) throws SQLException
    {
        Connection c = transactionConnection.get();
        if (c == null)
        {
            return;
        }

        try
        {
            if (commit)
            {
                c.commit();
            }
            else
            {
                c.rollback();
            }
        }
        finally
        {
            c.setAutoCommit(true);
            transactionConnection.remove();
            releaseConnection(c);
        }
    }
    /**
     * Run {@code Callable<T>} as a transaction.<br>
     * all exceptions but {@code SQLException} threw by {@code Callable<T>} is wrapped into {@code RuntimeException}<br>
     * throw {@code NullPointerException} if {@code fun} be {@code null}<br>
     * @param <T>  type of return result
     * @param fun
     * @param transactionListener listener for transaction status
     * @return
     * @throws RuntimeDaoException
     */
    <T>T runAsTransaction(Callable<T> fun,TransactionListener transactionListener) throws RuntimeDaoException{
    	checkArgument(fun != null, "fun is null");
    	checkArgument(null != transactionListener,"transactionListener is null");
        try {
            beginTransaction();
            transactionListener.onBegin();
            boolean commit = false;
            try {
                T result = fun.call();
                commit = true;
                transactionListener.onCommit();
                return result;
            } catch (SQLException e) {
                throw e;
            } catch (RuntimeException e) {
                throw e;
            }catch (Exception e) {
                throw new RuntimeException(e);
            }finally {
                endTransaction(commit);
                transactionListener.onEnd();
            }
        } catch (SQLException e) {
            throw new RuntimeDaoException(e);
        }
    }
    
    /**
	 * Run {@code Runnable} as a transaction.no return
	 * @param fun
	 * @param transactionListener listener for transaction status
	 * @throws RuntimeDaoException
	 * @see #runAsTransaction(Callable,TableListener.TransactionListener)
	 */
	void runAsTransaction(final Runnable fun,TransactionListener transactionListener) throws RuntimeDaoException{
		checkArgument(fun != null, "fun is null");
	    runAsTransaction(new Callable<Object>(){
	
	        @Override
	        public Object call() throws Exception {
	            fun.run();
	            return null;
	        }},transactionListener);
	}

    @Override
    public <T>T runAsTransaction(Callable<T> fun) throws RuntimeDaoException{
        return runAsTransaction(fun, ListenerContainerLocal.TRANSACTION_LISTENER);
    }

    @Override
    public void runAsTransaction(Runnable fun) throws RuntimeDaoException{
        runAsTransaction(fun,ListenerContainerLocal.TRANSACTION_LISTENER);
    }

    @Override
	public <T> T runWithNoPage(Callable<T> fun) throws RuntimeDaoException{
		checkArgument(fun != null, "fun is null");
		@SuppressWarnings("rawtypes")
		Page page = PageHelper.getLocalPage();
		boolean enable = false;
		if(null != page && (enable = page.isEnable())){    			
			PageHelper.setEnable(false);
		}
		try {
			return fun.call();
		} catch (Exception e) {
			Throwables.throwIfInstanceOf(e, RuntimeDaoException.class);
			Throwables.throwIfUnchecked(e);
			throw new RuntimeDaoException(e);				
		}finally {
			if(null != page && enable){
				PageHelper.setEnable(enable);
			}
		}
	}

	@Override
	public void runWithNoPage(Runnable fun) throws RuntimeDaoException{
    	if(null != fun){
    		runWithNoPage(new Callable<Boolean>() {

				@Override
				public Boolean call() throws Exception {
					fun.run();
					return false;
				}
			});
    	}
    }
    /**
     * Closes the passed Statement.
     */
    void close(Statement s)
    {
        try
        {
            if (s != null) {
                s.close();
            }
        }
        catch (SQLException x)
        {
            log("Could not close statement!: " + x.toString());
        }
    }

    /**
     * Closes the passed ResultSet.
     */
    void close(ResultSet rs)
    {
        try
        {
            if (rs != null) {
                rs.close();
            }
        }
        catch (SQLException x)
        {
            log("Could not close result set!: " + x.toString());
        }
    }

    /**
     * Closes the passed Statement and ResultSet.
     */
    void close(Statement s, ResultSet rs)
    {
        close(rs);
        close(s);
    }

	////////////////////////////////////////////////////
	// Helper methods for fetching numbers using IDs or names
	////////////////////////////////////////////////////

    /**
     * return all bytes in buffer (position~limit),no change status of buffer
     * @param buffer
     * @return
     */
    private static final byte[] getBytesInBuffer(ByteBuffer buffer){
    	if(null == buffer){
    		return null;
    	}
        int pos = buffer.position();
        try{
            byte[] bytes = new byte[buffer.remaining()];
            buffer.get(bytes);
            return bytes;
        }finally{
            buffer.position(pos);
        }
    }
    /**
     * Set a byte array to the passed prepared statement as a ByteBuffer or as null.
     */
    private static void  setBytes(int sqlType,PreparedStatement ps, int pos, ByteBuffer bytes) throws SQLException
    {
        setBytes(sqlType,ps,pos,getBytesInBuffer(bytes));
    }
    /**
     * Set a byte array to the passed prepared statement as a ByteBuffer or as null.
     */
    private static void  setBytes(int sqlType,PreparedStatement ps, int pos, byte[] bytes) throws SQLException
    {
        if (null == bytes){
            ps.setNull(pos, sqlType);
        }else{
            ps.setBytes(pos, bytes);
        }
    }
    /**
     * Retrieves a date value from the passed result set as a Calendar object.
     */
    static Calendar getCalendar(ResultSet rs, int pos) throws SQLException
    {
    	Timestamp date = rs.getTimestamp(pos);
        if (rs.wasNull()) {
            return null;
        }
        Calendar calendar = Calendar.getInstance();
    	calendar.setTime(date);
        return calendar;
    }

    /**
     * Retrieves a date value from the passed result set as a Calendar object.
     */
    static Calendar getCalendar(ResultSet rs, String column) throws SQLException
    {
        Timestamp date = rs.getTimestamp(column);
        if (null == date) {
            return null;
        }
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar;
    }

    /**
     * Set a Calendar object to the passed prepared statement as a date or as null.
     */
    static void  setCalendar(PreparedStatement ps, int pos, Calendar calendar) throws SQLException
    {
        if (calendar == null)
        {
            ps.setNull(pos, Types.TIMESTAMP);
        }
        else
        {
            ps.setTimestamp(pos, new java.sql.Timestamp(calendar.getTimeInMillis()));
        }
    }

    /**
     * 
     * For compatibility with old version {@link ResultSet} without getObject(int,Class) method
     * @param rs
     * @param columnIndex
     * @param type
     * @return
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
    <T> T getObject(ResultSet rs,int columnIndex, Class<T> type) throws SQLException {
        if (type == null) {
            throw new SQLException("Type parameter can not be null");
        }
        Object t = null;
        if (type.equals(String.class)) {
            t = rs.getString(columnIndex);
        } else if (type.equals(BigDecimal.class)) {
            t =  rs.getBigDecimal(columnIndex);
        } else if (type.equals(Boolean.class) || type.equals(Boolean.TYPE)) {
            t =  Boolean.valueOf(rs.getBoolean(columnIndex));
        } else if (type.equals(Byte.class) || type.equals(Byte.TYPE)) {
            t =  Byte.valueOf(rs.getByte(columnIndex));
        } else if (type.equals(Short.class) || type.equals(Short.TYPE)) {
            t =  Short.valueOf(rs.getShort(columnIndex));
        } else if (type.equals(Integer.class) || type.equals(Integer.TYPE)) {
            t =  Integer.valueOf(rs.getInt(columnIndex));
        } else if (type.equals(Long.class) || type.equals(Long.TYPE)) {
            t =  Long.valueOf(rs.getLong(columnIndex));
        } else if (type.equals(Float.class) || type.equals(Float.TYPE)) {
            t =  Float.valueOf(rs.getFloat(columnIndex));
        } else if (type.equals(Double.class) || type.equals(Double.TYPE)) {
            t =  Double.valueOf(rs.getDouble(columnIndex));
        } else if (type.equals(byte[].class)) {
            t =  rs.getBytes(columnIndex);
        } else if (type.equals(ByteBuffer.class)) {
        	byte[] array = rs.getBytes(columnIndex);
            t =  (array == null ? null : ByteBuffer.wrap(array));
        } else if (type.equals(java.sql.Date.class)) {
            t =  rs.getDate(columnIndex);
        } else if (type.equals(Time.class)) {
            t =  rs.getTime(columnIndex);
        } else if (type.equals(Timestamp.class)) {
            t =  rs.getTimestamp(columnIndex);
        } else if (type.equals(Calendar.class)) {
        	java.util.Date date = (java.util.Date) rs.getObject(columnIndex);
            if (date == null) {
                return null;
            }
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            t =  calendar;
        } else if (type.equals(Clob.class)) {
            t =  rs.getClob(columnIndex);
        } else if (type.equals(Blob.class)) {
            t =  rs.getBlob(columnIndex);
        } else if (type.equals(Array.class)) {
            t =  rs.getArray(columnIndex);
        } else if (type.equals(Ref.class)) {
            t =  rs.getRef(columnIndex);
        } else if (type.equals(URL.class)) {
            t =  rs.getURL(columnIndex);
        } else if (Geometry.class.isAssignableFrom(type)) {
        	// JTS Geomerty
        	t = getGeometryDataCodec().readGeometryData(rs,columnIndex);
        } else {
            Object obj = rs.getObject(columnIndex);
            try {
                t =  type.cast(obj);
            } catch (ClassCastException e) {
                if(null != extensiveResultSetTypeCast){
                    try{
                        t = extensiveResultSetTypeCast.cast(obj, type);
                    } catch (ClassCastException e2) {
                        throw new SQLException("Conversion not supported for type " + type.getName(), e2);
                    }
                }else{
                    throw new SQLException("Conversion not supported for type " + type.getName(), e);
                }
            }
        }
        return rs.wasNull() ? null : (T)t;
    }
    static String buildProcedureCall(String packageName, String procedureName, int paramCount) {
        return buildProcedureCall(packageName + "." + procedureName, paramCount);
    }
    
    static String buildProcedureCall(String procedureName, int paramCount) {
        StringBuilder sb = new StringBuilder("{call ").append(procedureName).append("(");
        for (int n = 1; n <= paramCount; n++) {
            sb.append("?,");
        }
        if (paramCount > 0) {
            sb.setLength(sb.length()-1);
        }
        return sb.append(")}").toString();
    }
    /**
     * @param targetTypes
     * @param ps
     * @param action
     * @return the count dealt by action
     * @since 3.18.3
     */
    int runPreparedStatementAsList(Map<String, Class<?>> targetTypes, PreparedStatement ps, Action<BaseBean> action){
    	checkArgument(null !=ps, "ps is null");
    	checkArgument(null !=action, "action is null");
        ResultSet rs = null;
        try {
            ps.setFetchSize(100);
            rs = ps.executeQuery();
            UnnameRowMetaData metaData = new UnnameRowMetaData(rs.getMetaData(), targetTypes);
            int count = 0;
            while(rs.next()){
            	BaseBean row = new UnnameRow(metaData);
            	for(int i=0; i<metaData.defaultColumnIdList.length; ++i){
            	    row.setValue(i, getObject(rs,i+1,metaData.fieldTypeOf(i)));
            	}
            	action.call(row);
            	count ++;
            }
            return count;
        } catch (SQLException e) {
            throw new RuntimeDaoException(new DataAccessException(e));
        } finally {
            this.close(rs);
        }
    }
    List<BaseBean> runPreparedStatementAsList(Map<String, Class<?>> targetTypes,PreparedStatement ps){
    	ListAction<BaseBean> action = new ListAction<>();
    	runPreparedStatementAsList(targetTypes,ps,action);
    	return action.getList();
    }
    @Override
	public List<BaseBean> runSqlAsList(Map<String, Class<?>> targetTypes, String sql, Object... argList){
    	checkArgument(!Strings.isNullOrEmpty(sql),"sql is null or empty");
        PreparedStatement ps = null;
        Connection c = null;
        try {
            c = this.getConnection();
            AtomicLong count = new AtomicLong(-1L);
            /** 先对SQL执行注入攻击检查及归一化处理 */
            sql = getStatementCache().normalize(sql, true);
            String wrapped = rebuildSelectSql(c,sql, argList, 1, -1, count, DataSourceConfig.isDebugOutput());
            // 执行count语句返回空表,就不必再继续执行SQL查询
            if(0 == count.get()){
            	return new ListAction<>().getList();
            }
            sql = firstNonNull(wrapped, sql);
            ps = getStatementCache().prepareStatement(c, sql, false, DataSourceConfig.isDebugOutput(), 
                    "runSqlAsList", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            fillPrepareStatement(ps, argList);
            return runPreparedStatementAsList(targetTypes,ps);
        } catch (SQLException e) {
            throw new RuntimeDaoException(new DataAccessException(e));
        } finally {
            this.close(ps);
            this.releaseConnection(c);
        }
    }
    
    @Override
    public List<Map<String, Object>> runSqlForMap(Map<String,Class<?>> targetTypes, String sql,Object... argList) throws RuntimeDaoException{
    	List<BaseBean> list = runSqlAsList(targetTypes, sql, argList);
		return new ArrayList<>(Lists.transform(list, input->null ==input ? null : new HashMap<>(input.asNameValueMap())));
    }
    
    private <T> List<T> fetchOnlyOneColumn(List<BaseBean> list,final Class<T> targetType){
    	if(list.isEmpty()){
    		return Collections.emptyList();
    	}
    	BaseBean row = list.get(0);
    	Object[] values = row.asValueArray();
    	
    	checkArgument(values.length == 1, "more than  1 column returned");
    	if(null == targetType){
    	    return new ArrayList<>(Lists.transform(list,input->null ==input ? null : input.getValue(0)));
    	}else{
    	    return new ArrayList<>(Lists.transform(list,input->null ==input ? null : targetType.cast(input.getValue(0))));
    	}
    }
    <T> List<T> runPreparedStatementAsList(Class<T> targetType, PreparedStatement ps) throws RuntimeDaoException{
    	List<BaseBean> list = runPreparedStatementAsList((Map<String,Class<?>>)null, ps);
    	return fetchOnlyOneColumn(list,targetType);
    }
    
    @Override
	public List<BaseBean> runSqlAsList(String sql, Object... argList) throws RuntimeDaoException{
		return runSqlAsList(Collections.<String,Class<?>>emptyMap(), sql, argList);
	}
	@Override
    public <T> List<T> runSqlAsList(Class<T> targetType, String sql, Object... argList) throws RuntimeDaoException{
    	List<BaseBean> list = runSqlAsList((Map<String,Class<?>>)null, sql, argList);
    	return fetchOnlyOneColumn(list,targetType);
    }
    
    @Override
    public <T> T runSqlForValue(Class<T> targetType,String sql, Object... argList) throws RuntimeDaoException{
    	List<T> list = runSqlAsList(targetType,sql,argList);
    	checkArgument(list.size() == 1,"more than  1 row returned for SQL: %s",sql);
    	return list.get(0);
    }
    @Override
	public boolean runSql(String sql, Object[] argList) {
		checkArgument(!Strings.isNullOrEmpty(sql),"sql is null or empty");
	    if(DataSourceConfig.isDebugOutput()){
	    	log("runSql:" + sql);
	    }
		PreparedStatement ps = null;
		Connection c = null;
		try {
			c = getConnection();
            ps = getStatementCache().prepareStatement(c, sql, true, DataSourceConfig.isDebugOutput(), "runSql");
			fillPrepareStatement(ps, argList);
			return ps.execute();
		} catch (SQLException e) {
			throw new RuntimeDaoException(e);
		} finally {
			close(ps);
			releaseConnection(c);
		}
	}

	@Override
	public int runSql(String sql) {
		checkArgument(!Strings.isNullOrEmpty(sql),"sql is null or empty");
	    if(DataSourceConfig.isDebugOutput()){
	    	log("runSql:" + sql);
	    }
	    Statement stat = null;
		Connection c = null;
		//System.out.printf("sql string:\n" + sql + "\n");
		try {
			c = getConnection();
			stat = c.createStatement();
			return stat.executeUpdate(sql);
		} catch (SQLException e) {
			throw new RuntimeDaoException(e);
		} finally {
			close(stat);
			releaseConnection(c);
		}
	}
    @Override
    public long rowCountOf(String sql) throws RuntimeDaoException{
        Connection c = null;
        try {
            c = this.getConnection();
            return rowCountOf(c, sql,null, null, DataSourceConfig.isDebugOutput(), true);
        } catch (SQLException e) {
            throw new RuntimeDaoException(e);
        } finally {
            releaseConnection(c);
        }
    }
	<T> T runPreparedStatementForValue(Class<T> targetType,PreparedStatement ps) throws RuntimeDaoException{
		return runWithNoPage(()->{
			List<T> list = runPreparedStatementAsList(targetType,ps);
			checkArgument(list.size() == 1,"more than  1 row or none row returned");
			return list.get(0);
		});
    }
	/**
     * 填充PreparedStatement中的参数对象
     * 
     * @param ps
     * @param argList
     * @throws SQLException
     */
    protected void fillPrepareStatement(PreparedStatement ps, Object[] argList) throws SQLException{
        if (!(argList == null || ps == null)) {
            for (int i = 0; i < argList.length; i++) {
            	if (argList[i] instanceof ByteBuffer) {	
                    ps.setBytes(i + 1, getBytesInBuffer((ByteBuffer) argList[i]));
                } if (argList[i] instanceof Geometry) {
                    ps.setBytes(i + 1, getGeometryDataCodec().toWKB(argList[i]));
                } else {
                    ps.setObject(i + 1, argList[i]);
                }
            }
        }

    }
    /**
     * 填充PreparedStatement中的参数对象
     * @param ps
     * @param pos parameter Index, the first parameter is 1, the second is 2, ...
     * @param value parameter value
     * @param sqlType the SQL type code defined in java.sql.Types
     * @param columnTypeName type name of column
     * @throws SQLException
     */
    protected void fillPreparedStatement(PreparedStatement ps,int pos,Object value,int sqlType, String columnTypeName)
			throws SQLException {
		if(value instanceof ByteBuffer){
			setBytes(sqlType, ps, pos, (ByteBuffer)value);
		} else if(getGeometryDataCodec().isGeometryDataType(columnTypeName)) {
			if(sqlType == Types.OTHER) {
				setBytes(sqlType, ps, pos, getGeometryDataCodec().toWKB(value));
			}else if(String.class.equals(getJavaClass(sqlType))) {
				try {
					ps.setObject(pos,getGeometryDataCodec().toWKT(value),sqlType);
				} catch (ParseException e) {
					throw new SQLException(e);
				}
			}else {
				// UNKONW TO DO
				ps.setObject(pos,value,sqlType);
			}
		}else{
			ps.setObject(pos,value,sqlType);
		}
	}
	
	
	/**
	 * For those who do not want to read below, please simply pick up the
	 * configuration associated with your database.
	 * 
	 * More explanation:
	 * When you save a bean whose primary key is numeric and has no value set,
	 * we assume that you want sql2java to retrieve a key's value generated
	 * on the database side.
	 * 
	 * generatedkey.retrieve can take 3 values:
	 * 
	 * auto - the standard approach when you have a JDBC 3.0 driver.
	 * 
	 * before - the key's value is retrieved before inserting the record.
	 * 
	 * after - the key's value is retrieved after inserting the record 
	 * @author guyadong
	 *
	 */
	enum AutoKeyRetrieveType{
		auto,after,before
	}
	/**
	 * 
	 * For those who do not want to read below, please simply pick up the
	 * configuration associated with your database.
	 * 
	 * More explanation:
	 * When you save a bean whose primary key is numeric and has no value set,
	 * we assume that you want sql2java to retrieve a key's value generated
	 * on the database side.
	 * 
	 * If you set it to before or after you also need to configure the
	 * autogeneratedkey.statement properties.
	 * <TABLE> is replaced at code generation time by the table name.
	 * <KEY> is replaced at code generation time by the column name.
	 * You may adjust this properties to fit your own naming convention.
	 * 
	 * PICK THE CONFIGURATION ASSOCIATED WITH YOUR DATABASE
	 * (or create one, but in that case let us know so we can add it here... :-)
	 */
	private static final ImmutableMap<String, String> AUTOINC_MECHANISM = ImmutableMap.<String, String>builder()
		/*-- HSQL ------*/
		.put("hsql.generatedkey.retrieve","after")
		.put("hsql.generatedkey.statement","CALL IDENTITY()")
		/*-- ORACLE 8i ------*/
		.put("oracle.generatedkey.retrieve","before")
		.put("oracle.generatedkey.statement","SELECT SEQ_<TABLE>.nextval FROM DUAL")
		/*-- MaxDB (SapDB) ------*/
		.put("maxdb.generatedkey.retrieve","before")
		.put("maxdb.generatedkey.statement","SELECT SEQ_<TABLE>_<KEY>.nextval FROM DUAL")
		/*-- MYSQL (without jdbc 3.0 driver) ------*/
		.put("mysql.generatedkey.retrieve","after")
		.put("mysql.generatedkey.statement","SELECT last_insert_id()")
		.put("mysql.page.query","LIMIT_OFFSET")
		/*-- SQL SERVER ------*/
		.put("sqlserver.generatedkey.retrieve","after")
		.put("sqlserver.generatedkey.statement","SELECT @@IDENTITY")
		.put("sqlserver.page.query","OFFSET_FETCH")
		/*-- SYBASE ------*/
		.put("sybase.generatedkey.retrieve","after")
		.put("sybase.generatedkey.statement","SELECT @@IDENTITY")
		/*-- POSTGRESQL ------*/
		.put("postgresql.generatedkey.retrieve","before")
		.put("postgresql.generatedkey.statement","SELECT nextval('<TABLE>_<KEY>_SEQ')")
		.put("postgresql.page.query","LIMIT_OFFSET")
		/*-- INFORMIX ------*/
		.put("informix.generatedkey.retrieve","after")
		.put("informix.generatedkey.statement","SELECT dbinfo('sqlca.sqlerrd1') FROM systables WHERE tabid=1")
		/*-- FIREBIRD ------
		 - need to create "sequence" number table first "create generator SEQ_<TABLE>"
		 - the 1 is how much to increment the sequence*/
		.put("firebird.generatedkey.retrieve","before")
		.put("firebird.generatedkey.statement","SELECT GEN_ID(SEQ_<TABLE>, 1) FROM RDB$DATABASE")
		/*-- DERBY ------*/
		.put("derby.generatedkey.retrieve","after")
		.put("derby.generatedkey.statement","VALUES IDENTITY_VAL_LOCAL()")
		.put("sqlite.page.query","LIMIT_OFFSET")
		/*-- PHOENIX(not testing) ------*/
		.put("phoenix.generatedkey.retrieve","before")
		.put("phoenix.generatedkey.statement","SELECT NEXT VALUE FOR SEQ_<TABLE>_<KEY>")
		.put("phoenix.insert.values","false")
		.put("phoenix.page.query","LIMIT_OFFSET")
		.build();
	
	private static final String getMechanismPropertyWithSuffix(String productName,String suffix) {
		StringTokenizer token = new StringTokenizer(productName);
		while(token.hasMoreTokens()){
			String value = AUTOINC_MECHANISM.get(token.nextToken().toLowerCase() + "." + suffix);
			if(value != null){
				return value;
			}
		}
		throw new NullPointerException(String.format( "NOT FOUND %s property for %s database",suffix,productName));
	}
	
	private synchronized void autokeyMechanismInit() throws SQLException{
		if(getDatabaseMetaData().supportsGetGeneratedKeys()){
			config.generatedkeyRetrieve =  "auto";
			config.generatedkeyStatement = null;
		}else{
			config.generatedkeyRetrieve = getMechanismPropertyWithSuffix(getProductName(),RETRIEVE_SUFFIX).toLowerCase();
			config.generatedkeyStatement = getMechanismPropertyWithSuffix(getProductName(),STATEMENT_SUFFIX);
		}
	}
	private void initPageQueryImplType(){

		config.pageQueryImplType = PageQueryImplType.NO_IMPL;
		try {
			config.pageQueryImplType = PageQueryImplType.valueOf(getMechanismPropertyWithSuffix(getProductName(),PAGE_QUERY_SUFFIX).toUpperCase());
		} catch (NullPointerException e) {
		} catch (IllegalArgumentException e) {
		}
		
	}
	PageQueryImplType getPageQueryImplType(){
		// double check
		if(config.pageQueryImplType == null){
			synchronized (this) {
				if(config.pageQueryImplType == null){
					initPageQueryImplType(); 
				}
			}
		}
		return config.pageQueryImplType;
	}
	String getGeneratedkeyRetrieve() throws SQLException{
		// double check
		if(config.generatedkeyRetrieve == null){
			synchronized (this) {
				if(config.generatedkeyRetrieve == null){
					autokeyMechanismInit();
				}
			}
		}
		return config.generatedkeyRetrieve;
	}
	boolean isSupportInsertValues() throws SQLException{
	    // double check
	    if(config.supportInsertValues == null){
	        synchronized (this) {
	            if(config.supportInsertValues == null){
	                config.supportInsertValues = true;
	                try {
	                    config.supportInsertValues = 
	                            Boolean.valueOf(getMechanismPropertyWithSuffix(getProductName(),INSERT_VALUES_SUFFIX).toLowerCase());
	                } catch (NullPointerException e) {
	                } catch (IllegalArgumentException e) {
	                }
	            }
	        }
	    }
	    return config.supportInsertValues;
	}
	
	AutoKeyRetrieveType getGeneratedkeyRetrieveType() throws SQLException{
		if(retrieveType == null){
			synchronized (this) {
				if(retrieveType == null){
					retrieveType = AutoKeyRetrieveType.valueOf(getGeneratedkeyRetrieve());
				}
			}
		}
		return retrieveType;
	}
	String getGeneratedkeyStatement(){
		// double check
		if(config.generatedkeyStatement == null &&  !AutoKeyRetrieveType.auto.equals(retrieveType)){
			synchronized (this) {
				if(config.generatedkeyStatement == null &&  !AutoKeyRetrieveType.auto.equals(retrieveType)){
					try {
						autokeyMechanismInit();
					} catch (SQLException e) {
						throw new RuntimeException(e);
					}
				}
			}
		}
		return config.generatedkeyStatement;
	}
	FireType getFireType(){
		return config.fireType;
	}
    /**
     * fill the given prepared statement with the bean values and a search type
     * @param metaData 
     * @param ps the PreparedStatement that will be filled
     * @param bean the bean to use for creating the where clauses
     * @param searchType exact ?  like ? starting like ?
     * @param fillNull wether fill null for null field
     * @param checkModified check column modified flag if true 
     * @param fieldList table of the field's associated constants,if null as all field
     * @param dirtyCount 
     * @return the number of clauses returned
     */
    int fillPreparedStatement(RowMetaData metaData,PreparedStatement ps, BaseBean bean, int searchType,boolean fillNull, boolean checkModified, int[] fieldList, int dirtyCount) throws DaoException
    {
        if (bean == null) {
            return 0;
        }
        try
        {
        	fieldList = firstNonNull(fieldList, metaData.defaultColumnIdList);
        	for(int columnId:fieldList){
        		Object value = bean.getJdbcValue(columnId);
        		if(null != value || fillNull){
        			if(!checkModified || bean.isModified(columnId)){
        				if(String.class == metaData.columnTypeOf(columnId)){
        					switch (searchType) {
        					case SEARCH_EXACT:
        						fillPreparedStatement(ps, ++dirtyCount , value, metaData.sqlTypes[columnId], metaData.columnTypeNames.get(columnId));
        						break;
        					case SEARCH_LIKE:
        						fillPreparedStatement(ps, ++dirtyCount , SQL_LIKE_WILDCARD + value + SQL_LIKE_WILDCARD, metaData.sqlTypes[columnId], metaData.columnTypeNames.get(columnId));
        						break;
        					case SEARCH_STARTING_LIKE:
        						fillPreparedStatement(ps, ++dirtyCount , SQL_LIKE_WILDCARD + value, metaData.sqlTypes[columnId], metaData.columnTypeNames.get(columnId));
        						break;
        					case SEARCH_ENDING_LIKE:
        						fillPreparedStatement(ps, ++dirtyCount , value + SQL_LIKE_WILDCARD, metaData.sqlTypes[columnId], metaData.columnTypeNames.get(columnId));
        						break;
        					default:
        						throw new DaoException("Unknown search type : " + searchType);
        					}
        				}else{
        					fillPreparedStatement(ps, ++dirtyCount , value, metaData.sqlTypes[columnId], metaData.columnTypeNames.get(columnId));
        				} 
        			}
        		}
        	}
        }
        catch(SQLException e)
        {
            throw new DataAccessException(e);
        }
        return dirtyCount;
    }
    static class LocalFillPreparedStatementArgs{
    	BaseRow bean;
    	int searchType;
    	boolean fillNull;
		public LocalFillPreparedStatementArgs(BaseRow bean, int searchType, boolean fillNull) {
			super();
			this.bean = bean;
			this.searchType = searchType;
			this.fillNull = fillNull;
		}
    }
    private static final ThreadLocal<LocalFillPreparedStatementArgs> localFillPreparedStatementArgs = new ThreadLocal<>();
    static void setLocalfillPreparedStatement(BaseRow bean, int searchType,boolean fillNull){
    	localFillPreparedStatementArgs.set(new LocalFillPreparedStatementArgs(bean,searchType,fillNull));
    }
    static void removeLocalfillPreparedStatement(){
    	localFillPreparedStatementArgs.remove();
    }
    private void localFillPreparedStatement(PreparedStatement ps) throws DaoException{
    	LocalFillPreparedStatementArgs args = localFillPreparedStatementArgs.get();
    	if(null != args){
    		fillPreparedStatement(args.bean.fetchMetaData(),ps,args.bean,args.searchType,args.fillNull, true, null, 0);
    	}
    }
	/**
	 * return SQL SELECT statement,if not rebuilt,return {@code null}<br>
	 * 
	 * @param c 
	 * @param sql            SQL SELECT statement
	 * @param argList     the arguments to use fill given prepared statement,may be null
	 * @param startRow the start row to be used (first row = 1, last row=-1)
	 * @param numRows the number of rows to be retrieved (all rows = a negative number)
	 * @param count       [out]row count 
	 * @param debug     output debug information if {@code true}
	 * @return rebuilt SQL statement if {@code sql} be rebuilt
	 * @throws SQLException
	 */
	String rebuildSelectSql(Connection c, String sql,Object[] argList,Integer startRow, Integer numRows, AtomicLong count, boolean debug) throws SQLException{
		if(null != sql){
			startRow = firstNonNull(startRow, 1);
			numRows = firstNonNull(numRows, -1);
			PageQueryImplType pageQueryImplType = getPageQueryImplType();
	        String wrapped = pageQueryImplType.wrap(sql, startRow, numRows);
	        @SuppressWarnings("rawtypes")
	        Page page;
	        if(null != wrapped && null != (page = PageHelper.getLocalPage()) && page.isEnable() && page.isCount()){
	        	/**
	        	 * 计算查询记录的总数
	        	 */
	        	long total = rowCountOf(c,wrapped,argList, page.getCountColumn(), debug, false);
	        	page.setTotal(total);
	        	if(null != count){
	        	    count.set(total);
	        	}
	        }
	        return wrapped;
		}
		return sql;
	}

	/**
	 * return row count of sql
	 * @param c
	 * @param sql
	 * @param argList the arguments to use fill given prepared statement,may be null
	 * @param countColumn column name for count() function 
	 * @param debug
	 * @param injectAnalyze run injection attack analysis if true
	 * @throws SQLException
	 */
	long rowCountOf(Connection c, String sql, Object[] argList, String countColumn, boolean debug, boolean injectAnalyze) throws SQLException{
	    checkArgument(!Strings.isNullOrEmpty(sql),"sql is null or empty");
        /**
         * 计算查询记录的总数
         */
        String countSql = ParserSupport.countSql(sql, countColumn);
        PreparedStatement ps = null;
        try {
            ps = getStatementCache().prepareStatement(c, countSql, injectAnalyze, debug,
                    "rowCountOf",
                    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            fillPrepareStatement(ps, argList);
            localFillPreparedStatement(ps);
            return runPreparedStatementForValue(Long.class,ps);
        } finally {
            close(ps);
        }
	}
}
